MySQL integer types (TINYINT, SMALLINT, INT, BIGINT) can be defined as SIGNED or UNSIGNED. This affects the allowed range of values, how MySQL evaluates comparisons, how indexes are used, and what happens during overflow.

Key Differences Between SIGNED and UNSIGNED

1
SIGNED integers allow negative values, while UNSIGNED integers only store 0 and positive values.
2
UNSIGNED effectively doubles the maximum positive range (e.g., INT SIGNED max = 2,147,483,647 vs INT UNSIGNED max = 4,294,967,295).
3
Indexing works the same structurally, but the value distribution affects query selectivity and performance.
4
Comparisons behave differently when negative values are involved (SIGNED) vs always non-negative (UNSIGNED).

Impact on Storage Size

1
Storage size is identical between SIGNED and UNSIGNED versions of the same type.
2
Only the allowed numeric range differs; bytes used are the same.
3
Example: INT (SIGNED or UNSIGNED) always uses 4 bytes.

Impact on Query Results & Index Usage

1
UNSIGNED indexes may provide better selectivity when the dataset contains large positive-only values.
2
Comparisons between SIGNED and UNSIGNED columns may cause implicit type conversion, reducing index usage.
3
Sorting differs because SIGNED sorts negatives first, while UNSIGNED always starts from 0.

Example: A SIGNED column can store −100 to 100, but UNSIGNED cannot store negative numbers; attempts will cause errors or wrap-around.

Overflow Behavior Example (SIGNED vs UNSIGNED)

1
SIGNED TINYINT range: −128 to 127
2
UNSIGNED TINYINT range: 0 to 255
3
Inserting 130 into SIGNED TINYINT overflows and wraps to −126
4
Inserting −1 into UNSIGNED TINYINT becomes 255 (wrap-around)

Example: Demonstrating Overflow

Result: a = -126 and b = 255 due to wrapping behavior.